package com.cn.ccp.service.system;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.cn.ccp.bean.MenuBean;
import com.cn.ccp.service.BasicService;


public class MenuService extends BasicService {


	public boolean sava(MenuBean entity) {
		String sql = "insert into menu (menuname,tip,link,parentid,descript,image,submenu) values (?,?,?,?,?,?,?)";
		return jdbcTemplate.update(sql ,new Object[]{entity.getMenuName() ,entity.getTip(), entity.getLink(),entity.getParentid(),entity.getDescript(),entity.getImage(),entity.getSubmenu()}) > 0;
	}

	public boolean update(MenuBean entity) {
		String sql = "update menu set menuname=?,tip=?,link=?,parentid=?,descript=?,image=? ,submenu =? where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{entity.getMenuName() ,entity.getTip(), entity.getLink(),entity.getParentid(),entity.getDescript(),entity.getImage(),entity.getSubmenu() ,entity.getId()}) > 0;
	}
	
	public boolean checkLink(String link){
		String sql = "SELECT COUNT(*) FROM menu WHERE link = ?";
		return jdbcTemplate.queryForInt(sql,new Object[]{link})>0;
	}
	

	public boolean delete(int id) {
		String sql = "delete from menu where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{id}) > 0;
	}

	public Object findById(int id) {
		String sql = "SELECT m.* FROM menu m WHERE m.id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { id }, new MenuMapper());
	}
	
	public boolean menuNameIsAlready(int id ,String menuname) {
		String sql = "";
		Object[] objs = null ;
		if(id == 0 ){
			sql = "SELECT m.* FROM menu m  WHERE m.menuname = ? ";
			objs = new Object[] { menuname };
		}else{
			sql = "SELECT m.* FROM menu m  WHERE m.menuname = ? and m.id != ?";
			objs = new Object[] { menuname };
		}
		return jdbcTemplate.query(sql, objs, new MenuMapper()).size() > 0;
	}

	public List<MenuBean>  pageQuery(int start, int pagesize) {
		String sql = "SELECT m.* FROM menu m limit ?,?";
		return jdbcTemplate.query(sql, new Object[] { start,pagesize},new MenuMapper());
	}
	
	public List<MenuBean>  pageQuery() {
		String sql = "SELECT m.* FROM menu m ";
		return jdbcTemplate.query(sql ,new MenuMapper());
	}
	
	public MenuBean getBean(int id){
		String sql = "SELECT m.* FROM menu m where m.id != ?";
		return (MenuBean) jdbcTemplate.queryForObject(sql, new Object[] {id},new MenuMapper());
	}

	public int getCount() {
		String sql = "select count(1) from menu ";
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List<MenuBean> getMenuListByUserID(String userid){
//		String sql = "SELECT m.*,mp.menuname AS pmenuname FROM role_user ru "+
//					" LEFT JOIN role_nemu rn ON rn.role_id = ru.role_id "+
//					" LEFT JOIN menu m ON m.id = rn.menu_id "+
//					" LEFT JOIN menu mp ON mp.id = m.parentid "+
//					" WHERE ru.user_id = ? AND m.id > 0";
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" select * from menu where id in( ");
		sqlBuf.append(" SELECT distinct(menu_id) from role_nemu where role_id in (select role_id from role_user where user_id = ? ) order by menu_id ) ");
		
		
		return jdbcTemplate.query(sqlBuf.toString(), new Object[] {userid},new MenuMapper());
	}
	
	public List<MenuBean> getMenuByUserId(int userId){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT distinct m.* FROM role_nemu rn ");
		sql.append(" LEFT join  menu m ON rn.menu_id = m.id  ");
		sql.append(" LEFT JOIN role_user ru ON ru.role_id = rn.role_id ");
		sql.append(" WHERE ru.user_id = ? order by m.id  ");
		return jdbcTemplate.query(sql.toString(),new Object[]{userId},new MenuMapper());
	}
	
	
//	//查询当前父类为0的数据，作为菜单栏的现实
//	public List<MenuBean> getMenuListZero(int userid){
//		String sql = "SELECT m.* FROM menu m "+
//					 " LEFT JOIN role_nemu rn ON rn.menu_id = m.id "+
//					 " LEFT JOIN role_user ru ON ru.role_id = rn.role_id "+
//					 " WHERE ru.user_id = ? AND m.parentid = 0 order by m.id desc";
//		return jdbcTemplate.query(sql,new Object[]{userid},new MenuMapper());
//	}
//	//获取菜单子项数据
//	public List<MenuBean> getMenuListOfZero(int userid){
//		String sql = "SELECT m.* FROM menu m "+
//		 " LEFT JOIN role_nemu rn ON rn.menu_id = m.id "+
//		 " LEFT JOIN role_user ru ON ru.role_id = rn.role_id "+
//		 " WHERE ru.user_id = ? AND m.parentid != 0 order by m.id desc";
//		return jdbcTemplate.query(sql,new Object[]{userid},new MenuMapper());
//	}
	
	public List<MenuBean> getSelectRoleMenuList(String roleid){
		String sql = "SELECT m.* FROM role_nemu rn "+
					" LEFT JOIN menu m ON m.id = rn.menu_id "+
					" WHERE rn.role_id = ?  GROUP BY rn.menu_id";
		return jdbcTemplate.query(sql, new Object[] {roleid},new MenuMapper()); 
	}
	
	public List<MenuBean> getNoSeleteRoleMenuList(String roleid){
		String sql = "SELECT * FROM menu m "+
					" WHERE m.id NOT IN ( "+
					" SELECT rn.menu_id FROM role_nemu rn WHERE rn.role_id = ? )";
		return jdbcTemplate.query(sql, new Object[] {roleid},new MenuMapper()); 
	}
	
	public String getMenuName(int id){
		String sql = "SELECT m.menuname FROM menu m where m.id != ?";
		return (String) jdbcTemplate.queryForObject(sql, new Object[] {id}, String.class);
	}
	public class MenuMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			MenuBean bean = new MenuBean();
			bean.setId(rs.getInt("id"));
			bean.setMenuName(rs.getString("menuname"));
			bean.setTip(rs.getString("tip"));
			bean.setLink(rs.getString("link"));
			bean.setParentid(rs.getInt("parentid"));
			try {
				String parentid = rs.getString("parentid");
				if(parentid != null && !parentid.equals("") && !parentid.equals("0")){
					bean.setParentMenuName(getMenuName(bean.getId()));
				}else{
					bean.setParentMenuName("");
				}
			} catch (Exception e) {
				bean.setParentMenuName("0");
			}
			bean.setDescript(rs.getString("descript"));
			bean.setImage(rs.getString("image"));
			bean.setSubmenu(rs.getString("submenu"));
			return bean ;
		}
	}
}
